/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
package eypweb_bsc;

import java.net.URL;
import java.sql.*;
import java.util.ResourceBundle;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventType;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.geometry.Pos;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.input.MouseEvent;

/**
 *
 * @author Kohalmi Istvan
 */

// ertekpapirokat rogzito kepernyot megvalosito osztaly
public class SecuritiesRegistry implements Initializable {

    @FXML
    private Button btnNew;
    @FXML
    private Button btnMod;
    @FXML
    private Button btnCan;
    @FXML
    private Button btnRes;
    @FXML
    private Button btnCom;
    @FXML
    private Button btnEsc;
    @FXML
    private Label errorLabel;
    @FXML
    private TextField tfName;
    @FXML
    private TextField tfISIN;
    @FXML
    private TextField tfDenomination;
    @FXML
    private TextField tf71OJ;
    @FXML
    private TextField tf71OJ_C;
    @FXML
    private TextField tf71OK;
    @FXML
    private TextField tfExpiry;
    @FXML
    private TextField tfStart;
    @FXML
    private TextField tfEnd;
    @FXML
    private ChoiceBox cbInstrument;
    @FXML
    private ChoiceBox cbInvestment;
    @FXML
    private ChoiceBox cbCurrency;
    @FXML
    private ChoiceBox cbBase;
    @FXML
    private TableView<Securities> masterData;

    public SecuritiesRegistry() {
        super();
    }

    @FXML
    private void handleNewButtonAction(ActionEvent event) {
        if (btnNew.getText().equals("Új elem")) {
            btnMod.setDisable(true);
            btnCom.setDisable(true);
            btnRes.setDisable(true);
            btnCan.setDisable(false);
            btnNew.setText("Rendben");
            cbCurrency.getSelectionModel().select(0);
            cbInstrument.getSelectionModel().select(0);
            cbInvestment.getSelectionModel().select(0);
            cbBase.getSelectionModel().select(0);
            tfName.clear();
            tfISIN.clear();
            tfDenomination.clear();
            tf71OJ.clear();
            tf71OJ_C.clear();
            tf71OK.clear();
            tfExpiry.clear();
            tfStart.clear();
            tfEnd.clear();
        } else {
            if (tfName.getText().isEmpty() || tfISIN.getText().isEmpty()
                    || tfDenomination.getText().isEmpty() || tf71OJ.getText().isEmpty()
                    || tf71OJ_C.getText().isEmpty() || tf71OK.getText().isEmpty()) {
                errorLabel.setText("A mezőket kötelező kitölteni!");
                return;
            }
            try {
                Date start = Date.valueOf(tfStart.getText());
                Date end = Date.valueOf(tfEnd.getText());
                Date.valueOf(tfExpiry.getText());
                if (start.after(end)) {
                    errorLabel.setText("A kezdődátum nem lehet későbbi, mint a végdátum!");
                    return;
                }
            } catch (Exception e) {
                errorLabel.setText("A dátum nincs megfelelően formázva!");
                return;
            }
            Securities security = new Securities();
            security.setID(0);
            security.setName(tfName.getText());
            security.setISIN(tfISIN.getText());
            security.setDenomination(tfDenomination.getText());
            security.setTIOJ(tf71OJ.getText());
            security.setTIOJ_C(tf71OJ_C.getText());
            security.setTIOK(tf71OK.getText());
            security.setExpiry(tfExpiry.getText());
            security.setStartDate(tfStart.getText());
            security.setEndDate(tfEnd.getText());
            security.setCurrency(cbCurrency.getSelectionModel().getSelectedItem().toString());
            security.setInstrument(cbInstrument.getSelectionModel().getSelectedItem().toString());
            security.setInvestment(cbInvestment.getSelectionModel().getSelectedItem().toString());
            security.setAccountBase(cbBase.getSelectionModel().getSelectedItem().toString());
            security.setModified();
            masterData.getItems().add(security);
            btnMod.setDisable(false);
            btnRes.setDisable(false);
            btnCom.setDisable(false);
            btnNew.setText("Új elem");
        }
    }

    @FXML
    private void handleModifyButtonAction(ActionEvent event) {
        if (btnMod.getText().equals("Módosít")) {
            Securities selectedItem = masterData.getSelectionModel().getSelectedItem();
            if (selectedItem != null) {
                tfName.setText(selectedItem.getName());
                tfISIN.setText(selectedItem.getISIN());
                tfDenomination.setText(selectedItem.getDenomination());
                tf71OJ.setText(selectedItem.getTIOJ());
                tf71OJ_C.setText(selectedItem.getTIOJ_C());
                tf71OK.setText(selectedItem.getTIOK());
                tfExpiry.setText(selectedItem.getExpiry());
                cbInvestment.getSelectionModel().select(selectedItem.getInvestment());
                cbInstrument.getSelectionModel().select(selectedItem.getInstrument());
                cbCurrency.getSelectionModel().select(selectedItem.getCurrency());
                cbBase.getSelectionModel().select(selectedItem.getAccountBase());
                tfStart.setText(selectedItem.getStartDate());
                tfEnd.setText(selectedItem.getEndDate());
                btnMod.setText("Rendben");
                btnNew.setDisable(true);
                btnCom.setDisable(true);
                btnRes.setDisable(true);
                btnCan.setDisable(false);
                cbCurrency.setDisable(true);
                cbBase.setDisable(true);
            }
        } else {
            if (tfName.getText().isEmpty() || tfISIN.getText().isEmpty()
                    || tfDenomination.getText().isEmpty() || tf71OJ.getText().isEmpty()
                    || tf71OJ_C.getText().isEmpty() || tf71OK.getText().isEmpty()) {
                errorLabel.setText("A mezőket kötelező kitölteni!");
                return;
            }
            try {
                Date start = Date.valueOf(tfStart.getText());
                Date end = Date.valueOf(tfEnd.getText());
                Date.valueOf(tfExpiry.getText());
                if (start.after(end)) {
                    errorLabel.setText("A kezdődátum nem lehet későbbi, mint a végdátum!");
                    return;
                }
            } catch (Exception e) {
                errorLabel.setText("A dátum nincs megfelelően formázva!");
                return;
            }
            Securities selectedItem = masterData.getSelectionModel().getSelectedItem();
            if (selectedItem != null) {
                selectedItem.setName(tfName.getText());
                selectedItem.setISIN(tfISIN.getText());
                selectedItem.setDenomination(tfDenomination.getText());
                selectedItem.setTIOJ(tf71OJ.getText());
                selectedItem.setTIOJ_C(tf71OJ_C.getText());
                selectedItem.setTIOK(tf71OK.getText());
                selectedItem.setExpiry(tfExpiry.getText());
                selectedItem.setStartDate(tfStart.getText());
                selectedItem.setEndDate(tfEnd.getText());
                selectedItem.setCurrency(cbCurrency.getSelectionModel().getSelectedItem().toString());
                selectedItem.setInstrument(cbInstrument.getSelectionModel().getSelectedItem().toString());
                selectedItem.setInvestment(cbInvestment.getSelectionModel().getSelectedItem().toString());
                selectedItem.setAccountBase(cbBase.getSelectionModel().getSelectedItem().toString());
                selectedItem.setModified();
                btnMod.setText("Módosít");
                btnNew.setDisable(false);
                btnCom.setDisable(false);
                btnRes.setDisable(false);
                cbCurrency.setDisable(false);
                cbBase.setDisable(false);
            }
        }
    }

    @FXML
    private void handleCancelButtonAction(ActionEvent event) {
        btnNew.setDisable(false);
        btnMod.setDisable(false);
        btnRes.setDisable(false);
        btnCom.setDisable(false);
        btnNew.setText("Új elem");
        btnMod.setText("Módosít");
        btnCan.setDisable(true);
        cbCurrency.setDisable(false);
        cbBase.setDisable(false);
    }

    @FXML
    private void handleResetButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<Securities> securities = FXCollections.observableArrayList();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String SecuritiesQuery = "SELECT * FROM isin";
            PreparedStatement stmt = con.prepareStatement(SecuritiesQuery);
            ResultSet results = stmt.executeQuery();
            while (results.next()) {
                Securities security = new Securities();
                security.setID(results.getInt(1));
                security.setISIN(results.getNString(2));
                security.setName(results.getNString(3));
                security.setDenomination(Float.toString(results.getFloat(4)));
                security.setExpiry((results.getDate(5)==null)?"":results.getDate(5).toString());
                security.setTIOJ(Integer.toString(results.getInt(6)));
                security.setTIOJ_C(Integer.toString(results.getInt(7)));
                security.setTIOK(Integer.toString(results.getInt(8)));
                security.setStartDate(results.getDate(9).toString());
                security.setEndDate(results.getDate(10).toString());

                String InstrumentQuery = "SELECT eszkoz_csoport.megnev FROM eszkoz_csoport "
                        + "JOIN isin_ecs ON eszkoz_csoport.id = isin_ecs.id_ecs "
                        + "JOIN isin ON isin_ecs.id_isin = isin.id "
                        + "WHERE isin_ecs.hatalytol = "
                        + "(SELECT MAX(isin_ecs.hatalytol) FROM isin_ecs, isin "
                        + "WHERE isin_ecs.id_isin = isin.id AND isin.id=?) "
                        + "AND isin.id = ?";

                PreparedStatement stmt2 = con.prepareStatement(InstrumentQuery);
                stmt2.setInt(1, security.getID());
                stmt2.setInt(2, security.getID());
                ResultSet rs2 = stmt2.executeQuery();
                rs2.next();
                security.setInstrument(rs2.getNString(1));
                
                String InvestmentQuery = "SELECT befektetes_csoport.megnev FROM befektetes_csoport "
                        + "JOIN isin_bcs ON befektetes_csoport.id = isin_bcs.id_bcs "
                        + "JOIN isin ON isin_bcs.id_isin = isin.id "
                        + "WHERE isin_bcs.hatalytol = "
                        + "(SELECT MAX(isin_bcs.hatalytol) FROM isin_bcs, isin "
                        + "WHERE isin_bcs.id_isin = isin.id AND isin.id=?) "
                        + "AND isin.id = ?";
                
                PreparedStatement stmt3 = con.prepareStatement(InvestmentQuery);
                stmt3.setInt(1, security.getID());
                stmt3.setInt(2, security.getID());
                ResultSet rs3 = stmt3.executeQuery();
                rs3.next();
                security.setInvestment(rs3.getNString(1));

                String CurrencyQuery = "SELECT deviza.megnev FROM deviza "
                        + "JOIN isin_deviza ON deviza.id = isin_deviza.id_deviza "
                        + "JOIN isin ON isin_deviza.id_isin = isin.id "
                        + "WHERE isin_deviza.hatalytol = "
                        + "(SELECT MAX(isin_deviza.hatalytol) FROM isin_deviza, isin "
                        + "WHERE isin_deviza.id_isin = isin.id AND isin.id=?) "
                        + "AND isin.id = ?";
                
                PreparedStatement stmt4 = con.prepareStatement(CurrencyQuery);
                stmt4.setInt(1, security.getID());
                stmt4.setInt(2, security.getID());
                ResultSet rs4 = stmt4.executeQuery();
                rs4.next();
                security.setCurrency(rs4.getNString(1));
                
                String accountBaseQuery = "SELECT megnev FROM befektetes_alap "
                        + "WHERE id = ? " ;
                
                PreparedStatement stmt5 = con.prepareStatement(accountBaseQuery);
                stmt5.setInt(1, results.getInt(11));
                ResultSet rs5 = stmt5.executeQuery();
                rs5.next();
                security.setAccountBase(rs5.getNString(1));
                
                securities.add(security);
            }

            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
        masterData.getItems().clear();
        masterData.getItems().addAll(securities);
    }

    @FXML
    private void handleCommitButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        ObservableList<Securities> securities = masterData.getItems();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String insertAction = "exec AddOrModifyISIN ?,?,?,?,?,?,?,?,?,?,?,?,?,?";
            for (Securities secr : securities) {
                if (secr.getModified()){
                PreparedStatement insertStmt = con.prepareStatement(insertAction);
                    insertStmt.setInt(1, secr.getID());
                    insertStmt.setNString(2, secr.getISIN());
                    insertStmt.setNString(3, secr.getName());
                    insertStmt.setFloat(4, Float.valueOf(secr.getDenomination()));
                    insertStmt.setDate(5, secr.getExpiry().equals("")?null:Date.valueOf(secr.getExpiry()));
                    insertStmt.setInt(6, Integer.valueOf(secr.getTIOJ()));
                    insertStmt.setInt(7, Integer.valueOf(secr.getTIOJ_C()));
                    insertStmt.setInt(8, Integer.valueOf(secr.getTIOK()));
                    insertStmt.setDate(9, Date.valueOf(secr.getStartDate()));
                    insertStmt.setDate(10, Date.valueOf(secr.getEndDate()));
                    insertStmt.setNString(11, secr.getInstrument());
                    insertStmt.setNString(12, secr.getInvestment());
                    insertStmt.setNString(13, secr.getCurrency());
                    insertStmt.setNString(14, secr.getAccountBase());
                    insertStmt.execute();
                }
            }
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
    }

    @FXML
    private void handleEscapeButtonAction(ActionEvent event) {
        Director director = Director.getInstance();
        director.initializeMainMenu();
    }

    @FXML
    private void handleCSVButtonAction(ActionEvent event) {
        CSVHandler.exportSecurities(masterData.getItems());
    }

    @FXML
    private void handleXLSButtonAction(ActionEvent event) {
        XLSHandler.exportSecurities(masterData.getItems());
    }

    @FXML
    private void handleXMLButtonAction(ActionEvent event) {
        XMLHandler.exportSecurities(masterData.getItems());
    }
    
    @FXML
    private void handleRowSelect(MouseEvent event) {
        if (masterData.getSelectionModel().getSelectedItem() != null) {
            btnMod.setDisable(false);
        } else {
            btnMod.setDisable(true);
        }
    }

    public void initialize(URL url, ResourceBundle bundle) {
        
        handleResetButtonAction(new ActionEvent());
        Director director = Director.getInstance();
        AccessInfo ai = director.getAccessInfo();
        try {
            Connection con = DriverManager.getConnection("jdbc:sqlserver://" + ai.getIPAddress() + ":" + ai.getPort() + ";databaseName=" + ai.getDBName(), ai.getUser(), ai.getPass());
            String instrQuery = "SELECT megnev FROM eszkoz_csoport";
            PreparedStatement instrStmt = con.prepareStatement(instrQuery);
            ResultSet rsInstr = instrStmt.executeQuery();
            while (rsInstr.next())
            {
                cbInstrument.getItems().add(rsInstr.getNString(1));
            }
            
            String investQuery = "SELECT megnev FROM befektetes_csoport";
            PreparedStatement investStmt = con.prepareStatement(investQuery);
            ResultSet rsInvest = investStmt.executeQuery();
            while (rsInvest.next())
            {
                cbInvestment.getItems().add(rsInvest.getNString(1));
            }
            
            String currQuery = "SELECT megnev FROM deviza";
            PreparedStatement currStmt = con.prepareStatement(currQuery);
            ResultSet rsCurr = currStmt.executeQuery();
            while (rsCurr.next())
            {
                cbCurrency.getItems().add(rsCurr.getNString(1));
            }
            
            String baseQuery = "SELECT megnev FROM befektetes_alap";
            PreparedStatement baseStmt = con.prepareStatement(baseQuery);
            ResultSet rsBase = baseStmt.executeQuery();
            while (rsBase.next())
            {
                cbBase.getItems().add(rsBase.getNString(1));
            }
            
            con.close();
        } catch (SQLException sqle) {
            errorLabel.setText(sqle.getMessage());
        }
        
        cbCurrency.getSelectionModel().select(0);
        cbInstrument.getSelectionModel().select(0);
        cbInvestment.getSelectionModel().select(0);
        cbBase.getSelectionModel().select(0);

        TableColumn<Securities, String> isinCol = new TableColumn<Securities, String>("Azonosító");
        isinCol.setCellValueFactory(new PropertyValueFactory("isin"));
        
        TableColumn<Securities, String> nameCol = new TableColumn<Securities, String>("Megnevezés");
        nameCol.setCellValueFactory(new PropertyValueFactory("name"));

        TableColumn<Securities, String> denomCol = new TableColumn<Securities, String>("Alapcímlet");
        denomCol.setCellValueFactory(new PropertyValueFactory("denomination"));
        
        TableColumn<Securities, String> expiryCol = new TableColumn<Securities, String>("Lejárat");
        expiryCol.setCellValueFactory(new PropertyValueFactory("expiry"));

        TableColumn<Securities, String> ojCol = new TableColumn<Securities, String>("71OJ");
        ojCol.setCellValueFactory(new PropertyValueFactory("tioj"));
        
        TableColumn<Securities, String> oj_cCol = new TableColumn<Securities, String>("71OJ_C");
        oj_cCol.setCellValueFactory(new PropertyValueFactory("tioj_c"));
        
        TableColumn<Securities, String> okCol = new TableColumn<Securities, String>("71OK");
        okCol.setCellValueFactory(new PropertyValueFactory("tiok"));
        
        TableColumn<Securities, String> instrCol = new TableColumn<Securities, String>("Eszközcsoport");
        instrCol.setCellValueFactory(new PropertyValueFactory("instrument"));
        
        TableColumn<Securities, String> investCol = new TableColumn<Securities, String>("Befektetési csoport");
        investCol.setCellValueFactory(new PropertyValueFactory("investment"));
        
        TableColumn<Securities, String> currencyCol = new TableColumn<Securities, String>("Devizanem");
        currencyCol.setCellValueFactory(new PropertyValueFactory("currency"));
                
        TableColumn<Securities, String> startCol = new TableColumn<Securities, String>("Hatály kezdete");
        startCol.setCellValueFactory(new PropertyValueFactory("startDate"));

        TableColumn<Securities, String> endCol = new TableColumn<Securities, String>("Hatály vége");
        endCol.setCellValueFactory(new PropertyValueFactory("endDate"));
        
        TableColumn<Securities, String> baseCol = new TableColumn<Securities, String>("Elszámolás alapja");
        baseCol.setCellValueFactory(new PropertyValueFactory("accountBase"));
        
        masterData.getColumns().setAll(isinCol, nameCol, denomCol, currencyCol, expiryCol, instrCol, investCol, ojCol, oj_cCol, okCol, startCol, endCol,baseCol);
    }
}